package com.way.filemanager.providers;

import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.MatrixCursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.io.File;

public class FileListSQLiteOpenHelper extends SQLiteOpenHelper {
    private static final String TAG = "FileListSQLiteOpenHelper"; //$NON-NLS-1$
    public static final String[] QUERY_COLUMNS = {
        "_id",
        "path",
        "fullpath",
        "type_mime",
        "filename",
        "createtime",
        "modifytime"
        };
    private static final String QUERY_WHERE_FILENAME = "filename LIKE ";
    private static final String QUERY_WHERE_FILEPATH = " path LIKE ";
    private static final String QUERY_WHERE_FILEPATH_NO_CHILD_PATH = " path = ";
    private static final String QUERY_WHERE_DELETE_BY_ID = "_id=?";
    private static final String QUERY_WHERE_ORDER_BY_FULLPATH = "fullpath ASC";
    private static final String DATABASE_NAME = "filelist.db"; //$NON-NLS-1$
    private static final int DATABASE_VERSION = 1;
    private static final int ERR_ID = -1;
    private static final String DATABASE_TABLE_FILELIST = "filelist";
    public FileListSQLiteOpenHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        // TODO Auto-generated constructor stub
    }

    public String buildWhereFileSelect(String path,String filename,boolean IsIterative)
    {
        String where = null;
        if(filename != null)
            where = QUERY_WHERE_FILENAME + "'%" + filename +"%'";

        if((where != null)&&(path != null))
            where += " AND ";
        if(path != null)
        {
            String WheraPath = null;
            if(IsIterative)
                WheraPath = QUERY_WHERE_FILEPATH + "'"+path + File.separator +"%'";
            else
                WheraPath = QUERY_WHERE_FILEPATH_NO_CHILD_PATH + "'"+path +"'";
            if(where == null)
                where = WheraPath;
            else
                where += WheraPath;
        }


        return where;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        db.execSQL("CREATE TABLE FileList (" + //$NON-NLS-1$
                "_id INTEGER PRIMARY KEY," + //$NON-NLS-1$
                "path TEXT," +
                "fullpath TEXT," +
                "filename TEXT," +
                "type_mime INTEGER," +
                "createtime LONG," +
                "modifytime LONG" +
                ");"); //$NON-NLS-1$

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS filelist"); //$NON-NLS-1$
        onCreate(db);
    }
    /**
     * @param path :need Sync dir
     * @param m    :existing file list now.Must order by fullpath,asc
     *
     */

    public void sync(String path,Cursor mInputCursor,boolean IsIterative)
    {
        long temp = System.currentTimeMillis();
        Cursor fromDb =  query(path,null,0,null,IsIterative);
        Log.v(TAG, "sync  1:"+(System.currentTimeMillis()-temp));
        int[]InputId= new int[mInputCursor.getCount()];
        boolean[]DelFromDbId= new boolean[fromDb.getCount()];
        for(int id =0;id < InputId.length;id++)
        {
            InputId[id] = ERR_ID;
        }
        for(int id =0;id < DelFromDbId.length;id++)
        {
            DelFromDbId[id]=true;
        }
        Log.v(TAG, "sync  2:"+(System.currentTimeMillis()-temp));
        temp = System.currentTimeMillis();
        int dbFullpathIdx = fromDb.getColumnIndex(QUERY_COLUMNS[2]);
        int inputFullpathIdx = mInputCursor.getColumnIndex(QUERY_COLUMNS[2]);
        Log.v(TAG, "dbFullpathIdx:"+ dbFullpathIdx);
        Log.v(TAG, "inputFullpathIdx:"+ inputFullpathIdx);
        String InputCursorfullpath;
        String dbCursorfullpath;
        if(fromDb.getCount() <= mInputCursor.getCount())
        {
            fromDb.moveToPrevious();
            //while (fromDb.moveToNext())
            for(int index=0;index <fromDb.getCount();index++)
            {
                fromDb.moveToPosition(index);
                String fullpath = fromDb.getString(dbFullpathIdx);
                //Log.v(TAG, "index:"+ index);
                //Log.v(TAG, "fullpath:"+ fullpath);
                //mInputCursor.moveToPosition(position);
                //mInputCursor.moveToPrevious();
                //while (mInputCursor.moveToNext())
                for(int InputIndex=0;InputIndex <mInputCursor.getCount();InputIndex++)
                {
                    mInputCursor.moveToPosition(InputIndex);
                    InputCursorfullpath = mInputCursor.getString(inputFullpathIdx);
                    if(fullpath.equalsIgnoreCase(InputCursorfullpath))
                    {
                        //position = mInputCursor.getPosition();
                        InputId[InputIndex] = index;
                        break;
                    }
                }
            }
        }else{
            fromDb.moveToPrevious();
            //while (fromDb.moveToNext())
            for(int index=0;index <mInputCursor.getCount();index++)
            {
                mInputCursor.moveToPosition(index);
                String fullpath = mInputCursor.getString(dbFullpathIdx);
                //Log.v(TAG, "index:"+ index);
                //Log.v(TAG, "fullpath:"+ fullpath);
                //mInputCursor.moveToPosition(position);
                //mInputCursor.moveToPrevious();
                //while (mInputCursor.moveToNext())
                for(int dbIndex=0;dbIndex <fromDb.getCount();dbIndex++)
                {
                    fromDb.moveToPosition(dbIndex);
                    dbCursorfullpath = fromDb.getString(inputFullpathIdx);
                    if(fullpath.equalsIgnoreCase(dbCursorfullpath))
                    {
                        //position = mInputCursor.getPosition();
                        InputId[index] = dbIndex;
                        break;
                    }
                }
            }
        }

        Log.v(TAG, "sync  3:"+(System.currentTimeMillis()-temp));
        temp = System.currentTimeMillis();
        MatrixCursor InputForInsert = new MatrixCursor(QUERY_COLUMNS);
        int id;
        int QUERY_COLUMNS_0 = mInputCursor.getColumnIndex(QUERY_COLUMNS[0]);
        int QUERY_COLUMNS_1 = mInputCursor.getColumnIndex(QUERY_COLUMNS[1]);
        int QUERY_COLUMNS_2 = mInputCursor.getColumnIndex(QUERY_COLUMNS[2]);
        int QUERY_COLUMNS_3 = mInputCursor.getColumnIndex(QUERY_COLUMNS[3]);
        int QUERY_COLUMNS_4 = mInputCursor.getColumnIndex(QUERY_COLUMNS[4]);
        int QUERY_COLUMNS_5 = mInputCursor.getColumnIndex(QUERY_COLUMNS[5]);
        int QUERY_COLUMNS_6 = mInputCursor.getColumnIndex(QUERY_COLUMNS[6]);
        for(int index =0;index < InputId.length;index++)
        {
            id= InputId[index];
            if(id == ERR_ID)
            {
                mInputCursor.moveToPosition(index);
                 {
                    Object[] rowContent = new Object[QUERY_COLUMNS.length];
                    //rowContent[0] = mInputCursor.getInt(QUERY_COLUMNS_0);
                    if(QUERY_COLUMNS_1 != -1)
                        rowContent[1] = mInputCursor.getString(QUERY_COLUMNS_1);
                    if(QUERY_COLUMNS_2 != -1)
                        rowContent[2] = mInputCursor.getString(QUERY_COLUMNS_2);
                    if(QUERY_COLUMNS_3 != -1)
                        rowContent[3] = mInputCursor.getString(QUERY_COLUMNS_3);
                    if(QUERY_COLUMNS_4 != -1)
                        rowContent[4] = mInputCursor.getString(QUERY_COLUMNS_4);
                    if(QUERY_COLUMNS_5 != -1)
                        rowContent[5] = mInputCursor.getLong(QUERY_COLUMNS_5);
                    if(QUERY_COLUMNS_6 != -1)
                        rowContent[6] = mInputCursor.getLong(QUERY_COLUMNS_6);
                    InputForInsert.addRow(rowContent);
                    //Log.v(TAG, "insert file:"+mInputCursor.getString(QUERY_COLUMNS_2));
                }
            }
            else
                DelFromDbId[id] = false;
        }
        Log.v(TAG, "InputForInsert getCount:"+InputForInsert.getCount());


        Log.v(TAG, "sync  4:"+(System.currentTimeMillis()-temp));
        temp = System.currentTimeMillis();
        insert(InputForInsert);

        Log.v(TAG, "sync  5:"+(System.currentTimeMillis()-temp));
        temp = System.currentTimeMillis();
        InputForInsert.close();

        Log.v(TAG, "sync  6:"+(System.currentTimeMillis()-temp));
        temp = System.currentTimeMillis();
        MatrixCursor InputForDel = new MatrixCursor(QUERY_COLUMNS);
        QUERY_COLUMNS_0 = fromDb.getColumnIndex(QUERY_COLUMNS[0]);
        //int del_id = InputForDel.getColumnIndex(QUERY_COLUMNS[0]);
        for(int index =0;index < DelFromDbId.length;index++)
        {
            if(DelFromDbId[index])
            {
                fromDb.moveToPosition(index);
                {
                   Object[] rowContent = new Object[QUERY_COLUMNS.length];
                   //rowContent[0] = mInputCursor.getInt(QUERY_COLUMNS_0);
                   if(QUERY_COLUMNS_0 != -1)
                       rowContent[0] = fromDb.getInt(QUERY_COLUMNS_0);
                   Log.v(TAG, "delete file:"+fromDb.getString(QUERY_COLUMNS_2));
                   InputForDel.addRow(rowContent);
               }
            }
        }
        Log.v(TAG, "InputForDel getCount:"+InputForDel.getCount());

        Log.v(TAG, "sync  7:"+(System.currentTimeMillis()-temp));
        temp = System.currentTimeMillis();
        delete(InputForDel);

        Log.v(TAG, "sync  8:"+(System.currentTimeMillis()-temp));
        temp = System.currentTimeMillis();
        InputForDel.close();
        Log.v(TAG, "sync  9:"+(System.currentTimeMillis()-temp));
     fromDb.close();
    }

    public Cursor query(String path,String filename,int Limit,String szOrder,boolean IsIterative)
    {
        SQLiteDatabase db = this.getReadableDatabase();
        String szLimit = null;
        Log.v(TAG, "query  path:" + path);
        Log.v(TAG, "query  filename:" + filename);
        Log.v(TAG, "query  Limit:" + Limit);
        Log.v(TAG, "query  IsIterative:" + IsIterative);

        if(Limit > 0)
            szLimit = String.valueOf(Limit);
        Log.v(TAG, "query  szLimit:" + szLimit);
        if(szOrder == null)
            szOrder = QUERY_WHERE_ORDER_BY_FULLPATH;

        Cursor temp = db.query(DATABASE_TABLE_FILELIST,
                QUERY_COLUMNS,
                buildWhereFileSelect(path ,filename,IsIterative), null, null, null, szOrder,szLimit);
        Log.v(TAG, "query  temp.getCount:" + temp.getCount());
        temp.moveToPrevious();
        MatrixCursor ret = new MatrixCursor(QUERY_COLUMNS);
        int QUERY_COLUMNS_0 = temp.getColumnIndex(QUERY_COLUMNS[0]);
        int QUERY_COLUMNS_1 = temp.getColumnIndex(QUERY_COLUMNS[1]);
        int QUERY_COLUMNS_2 = temp.getColumnIndex(QUERY_COLUMNS[2]);
        int QUERY_COLUMNS_3 = temp.getColumnIndex(QUERY_COLUMNS[3]);
        int QUERY_COLUMNS_4 = temp.getColumnIndex(QUERY_COLUMNS[4]);
        int QUERY_COLUMNS_5 = temp.getColumnIndex(QUERY_COLUMNS[5]);
        int QUERY_COLUMNS_6 = temp.getColumnIndex(QUERY_COLUMNS[6]);
        try {
            while (temp.moveToNext()) {
                Object[] rowContent = new Object[QUERY_COLUMNS.length];
                rowContent[0] = temp.getInt(QUERY_COLUMNS_0);
                rowContent[1] = temp.getString(QUERY_COLUMNS_1);
                rowContent[2] = temp.getString(QUERY_COLUMNS_2);
                rowContent[3] = temp.getString(QUERY_COLUMNS_3);
                rowContent[4] = temp.getString(QUERY_COLUMNS_4);
                rowContent[5] = temp.getLong(QUERY_COLUMNS_5);
                rowContent[6] = temp.getLong(QUERY_COLUMNS_6);
                ret.addRow(rowContent);
                //Log.v("rwei", "Filename:" + rowContent[4]);
            }
        } finally {
            temp.close();
        }
        db.close();
        return ret;
    }

    @SuppressWarnings("deprecation")
    public int insert(Cursor m)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        //int QUERY_COLUMNS_0 = m.getColumnIndex(QUERY_COLUMNS[0]);
        int QUERY_COLUMNS_1 = m.getColumnIndex(QUERY_COLUMNS[1]);
        int QUERY_COLUMNS_2 = m.getColumnIndex(QUERY_COLUMNS[2]);
        int QUERY_COLUMNS_3 = m.getColumnIndex(QUERY_COLUMNS[3]);
        int QUERY_COLUMNS_4 = m.getColumnIndex(QUERY_COLUMNS[4]);
        int QUERY_COLUMNS_5 = m.getColumnIndex(QUERY_COLUMNS[5]);
        int QUERY_COLUMNS_6 = m.getColumnIndex(QUERY_COLUMNS[6]);
        DatabaseUtils.InsertHelper helper =
                new DatabaseUtils.InsertHelper(db, DATABASE_TABLE_FILELIST);
        int pathidx = helper.getColumnIndex(QUERY_COLUMNS[1]);
        int fullpathidx = helper.getColumnIndex(QUERY_COLUMNS[2]);
        int type_mimeidx = helper.getColumnIndex(QUERY_COLUMNS[3]);
        int filenameId = helper.getColumnIndex(QUERY_COLUMNS[4]);
        int createtime = helper.getColumnIndex(QUERY_COLUMNS[5]);
        int modifytime = helper.getColumnIndex(QUERY_COLUMNS[6]);
        Log.v(TAG, "pathidx:"+pathidx);
        m.moveToPrevious();
        db.beginTransaction();
        try {
            while (m.moveToNext()) {

                helper.prepareForInsert();
                helper.bind(pathidx, m.getString(QUERY_COLUMNS_1));
                helper.bind(fullpathidx, m.getString(QUERY_COLUMNS_2));
                helper.bind(type_mimeidx, m.getString(QUERY_COLUMNS_3));
                helper.bind(filenameId, m.getString(QUERY_COLUMNS_4));
                helper.bind(createtime, m.getLong(QUERY_COLUMNS_5));
                helper.bind(modifytime, m.getLong(QUERY_COLUMNS_6));
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
            // / M: Decrease transaction count.
            helper.close();
        }
        db.close();
        return 0;
    }
    public int delete(Cursor m)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        String[] whereArgs = new String[1];
        int QUERY_COLUMNS_0 = m.getColumnIndex(QUERY_COLUMNS[0]);
        if(QUERY_COLUMNS_0 < 0)
            return QUERY_COLUMNS_0;
        db.beginTransaction();
        try {
            while (m.moveToNext()) {
                whereArgs[0] = String.valueOf(m.getInt(QUERY_COLUMNS_0));
                db.delete(DATABASE_TABLE_FILELIST, QUERY_WHERE_DELETE_BY_ID, whereArgs);
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
        db.close();
        return 0;
    }

}
